package com.kunbo.cn.controller.core.card;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.fasterxml.jackson.annotation.JsonTypeInfo.Id;
import com.kunbo.cn.app.entity.Car;
//@RestController
@Controller
//@RequestMapping("/Other")
public class TestExportController {
	@Autowired
	private JdbcTemplate jdbc;
	@RequestMapping(value="exportDemo",method=RequestMethod.GET)
	//@GetMapping("/exportDemo")
	public void exportDemo(HttpServletResponse response) {
		//1.组装excel要导出的数据
		String sql = "SELECT id,mater_code,name,short_name,unit,note FROM business_materials_manage  ";
		List<Map<String,Object>> list=jdbc.queryForList(sql);
		Map map = new HashMap();
		map.put("id", "id");
		map.put("mater_code", "mater_code");
		map.put("name", "name");
		map.put("short_name", "short_name");
		map.put("unit", "unit");
		map.put("note", "note");
		list.add(map);
		
		
		//1.创建excel
		HSSFWorkbook wk = new HSSFWorkbook();
		CellStyle cs = wk.createCellStyle();
		cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);
		//2.创建一张工作表
		HSSFSheet sheet = wk.createSheet();
		sheet.setColumnWidth(0, 256*30+150);
		sheet.setColumnWidth(1, 256*20+150);
		sheet.setColumnWidth(2, 256*20+150);
		sheet.setColumnWidth(3, 256*20+150);
		sheet.setColumnWidth(4, 256*20+150);
		sheet.setColumnWidth(5, 256*20+150);
		
		sheet.setDefaultRowHeightInPoints(30*20);
		
		HSSFPrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张
		sheet.setDisplayGridlines(false);
		sheet.setPrintGridlines(false);
		sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.2 ); // 上边距
		sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.2 ); // 下边距
		sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.2 ); // 左边距
		sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.2 ); // 右边距
		//3.设置工作表的列宽
		//sheet.setColumnWidth(0, 5000);
		//4.设置excel表头（创建第一行）
		HSSFRow row0 = sheet.createRow(0);
		HSSFRow row = sheet.createRow(1);
		HSSFCell cell0 = row0.createCell(0);
		HSSFCell cell = row.createCell(0);
		
		cell0.setCellStyle(cs);
		cell0.setCellValue("易达智能洗煤厂");
		//cell0.setCellStyle(0x5);
		//cell0.setCellValue("1920-22-21:00");
		
		cell.setCellStyle(cs);
		cell.setCellValue("车牌号");
		
		cell = row.createCell(1);
		cell.setCellStyle(cs);
		cell.setCellValue("原发净重");
		
		cell = row.createCell(2);
		cell.setCellStyle(cs);
		cell.setCellValue("毛重");
		
		cell = row.createCell(3);
		cell.setCellStyle(cs);
		cell.setCellValue("皮重");
		
		cell = row.createCell(4);
		cell.setCellStyle(cs);
		cell.setCellValue("净重");
		
		cell = row.createCell(5);
		cell.setCellStyle(cs);
		cell.setCellValue("亏损");
		//5.设置excel导出数据
		 // 创建第一行
        for (short i=0;i<list.size();i++)
        {
            row = sheet.createRow(i+2);
            Cell cell1 = row.createCell(0);
            Cell cell2 = row.createCell(1);
            Cell cell3 = row.createCell(2);
            Cell cell4 = row.createCell(3);
            Cell cell5 = row.createCell(4);
            Cell cell6 = row.createCell(5);
          cell1.setCellValue(String.valueOf( list.get(i).get("id")));
          // System.out.println("打印：");
            cell1.setCellStyle(cs);
          //  String name1=String.valueOf(list.get(i).get("name"));
            cell2.setCellValue(String.valueOf(list.get(i).get("mater_code")));
            System.out.println("打印：----->"+list.get(i).get("mater_code"));
            cell2.setCellStyle(cs);
            cell3.setCellValue(String.valueOf(list.get(i).get("mater_code")));
            cell3.setCellStyle(cs);
            cell4.setCellValue(String.valueOf(list.get(i).get("name")));
            cell4.setCellStyle(cs);
           // cell5.setCellValue(list.get(i).getJizhong());
            cell5.setCellStyle(cs);
           // cell6.setCellValue(list.get(i).getKuisun());
            cell6.setCellStyle(cs);
        }
        
        CellRangeAddress  range= new CellRangeAddress(0,0,0,5);
        sheet.addMergedRegion(range);
		//6.设置excel统计数据
        try {
			response.setContentType("application/octet-stream");
			response.setHeader("Content-disposition", "attachment;filename=Opinion.xls");//默认Excel名称
			response.flushBuffer();
			wk.write(response.getOutputStream());
			wk.write(new FileOutputStream(new File("D://daochu/采购逐车明细.xls")));
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
	}

}

